﻿using System;
using System.Collections.Generic;
using System.Web;
using Guline.wsjx.SqlExcute;
using System.Data;
namespace Guline.wsjx.Models
{
    public class CommonFunction
    {
        private Provider db;
        public CommonFunction(string connectstring)
        {
            db = new Provider(connectstring);
        }
        public bool Login(string username, string password)
        {
            DataTable tb = db.excuteTable("SELECT cAccName FROM Account_Info WHERE cAccName = '"+username+"' AND cPassWord = '"+password+"'");
            if (tb.Rows.Count>0)
            {
                return true;
            }
            else return false;
        }
        //($user,$pass11,$pass21,$email,$quest,$answer,$phone)
        public bool register(string username, string password1,string password2,string email,string quest,string answer,string phone)
        {
            int result = 0;
            
            string sql1 = "INSERT INTO Account_Info (cAccName,cSecPassWord,cPassWord,iClientID,dLoginDate,dLogoutDate,nExtPoint,nExtPoint1,nExtPoint2,nExtPoint3,nExtPoint4,nExtPoint5,nExtPoint6,nExtPoint7,nUserIP,nUserIPPort,nFeeType,bParentalControl,bIsBanned,bIsUseOTP,iOTPSessionLifeTime,iServiceFlag) VALUES('" + username + "','" + password2 + "','" + password1 + "',0,'2014-04-17 04:33:12.173','2014-04-17 04:34:12.173',1,0,0,0,0,0,0,0,1,1,1,0,0,0,1,0) ";
            result = db.excuteUpdate(sql1);
            string sql2="INSERT INTO Account_Habitus (cAccName,iLeftSecond,dEndDate,iUseSecond) VALUES('"+username+"',0,'2015-03-02 00:00:00.000',796532) ";
            result = db.excuteUpdate(sql2);
            string sql3 = "INSERT INTO Account_sub_Info (cAccName,cPassWord,cPhone,cQuestion,cAnswer,cEMail) VALUES('" + username + "','" + password1 + "','" + phone + "','" + quest + "','" + answer + "','" + email+ "') ";
            result = db.excuteUpdate(sql3);
            if (result > 0)
            {
                return true;
            }
            else {
                return false;
            }
        }
        //re_password1($user,$pass11,$quest,$answer)
        public bool changpass1(string username, string pass1, string quest, string answer)
        {
            string sql1 = "SELECT * FROM Account_sub_Info WHERE cQuestion = '" + quest + "' AND cAnswer='" + answer + "' AND cAccName = '" + username + "'";
            DataTable tb = db.excuteTable(sql1);
            if (tb.Rows.Count > 0)
            {
                string sql2 = "UPDATE Account_Info SET cPassWord='" + pass1 + "' WHERE cAccName='" + username + "' ";
                db.excuteUpdate(sql2);
                return true;
            }
            else
            {
                return false;
            }

        }
        public bool changpass2(string username, string pass2,string pass2new)
        {
            string sql1 = "SELECT * FROM Account_Info WHERE cSecPassWord='"+pass2+"' AND cAccName = '" + username + "'; ";
            DataTable tb = db.excuteTable(sql1);
            if (tb.Rows.Count > 0)
            {
                string sql2 = "UPDATE Account_Info SET cSecPassWord='" + pass2new + "' WHERE cAccName='" + username + "' ";
                db.excuteUpdate(sql2);
                return true;
            }
            else
            {
                return false;
            }

        }
        public bool change_email(string username,string pass2, string email, string quest, string answer)
        {
            string sql1 = "SELECT * FROM Account_sub_Info WHERE cQuestion = '" + quest + "' AND cAnswer='" + answer + "' AND cAccName = '" + username + "'; ";
            DataTable tb = db.excuteTable(sql1);
            if (tb.Rows.Count > 0)
            {
                string sql3 = "select * from Account_Info where cSecPassWord='"+pass2+"' AND cAccName='"+username+"'";
                DataTable tb1 = db.excuteTable(sql3);
                if (tb1.Rows.Count > 0)
                {
                    string sql2 = "UPDATE Account_sub_Info SET cEMail='" + email + "' WHERE cAccName='" + username + "' ";
                    db.excuteUpdate(sql2);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }

        }
        public bool change_questanwser(string username, string pass2, string email, string quest, string answer)
        {
            string sql1 = "SELECT * FROM Account_sub_Info WHERE cEMail='"+email+"'  AND cAccName = '" + username + "'; ";
            DataTable tb = db.excuteTable(sql1);
            if (tb.Rows.Count > 0)
            {
                string sql3 = "select * from Account_Info where cSecPassWord='" + pass2 + "' AND cAccName='" + username + "'";
                DataTable tb1 = db.excuteTable(sql3);
                if (tb1.Rows.Count > 0)
                {
                    string sql2 = "UPDATE Account_sub_Info SET cQuestion = '" + quest + "',cAnswer='" + answer + "' WHERE cAccName='" + username + "' ";
                    db.excuteUpdate(sql2);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }

        }
        public bool editinfo(string question,string answer,string phone,bool banned,string username, string pass1, string pass2, string email, string nextpoint, string nextpoint1, string nextpoint2, string nextpoint3, string nextpoint4, string nextpoint5, string nextpoint6, string nextpoint7)
        {
            string setpass = "";
            if (pass1.Length > 6)
            {
                setpass += ",cPassWord= '"+pass1+"'";
            }
            if (pass2.Length > 6)
            {
                setpass += ",cSecPassWord= '"+pass2+"'";
            }
            string sql = "UPDATE Account_sub_Info SET cEMail= '" + email + "'" + setpass + ",cPhone='" + phone + "',cQuestion='" + question + "',cAnswer='"+answer+"' WHERE cAccName='" + username + "' ";
           db.excuteUpdate(sql);
            string sql2 = "UPDATE Account_Info SET bIsBanned="+(banned? 1:0)+",nExtPoint=" + nextpoint + ",nExtPoint1 =" + nextpoint1 + ",nExtPoint2 =" + nextpoint2 + ",nExtPoint3 =" + nextpoint3 + ",nExtPoint4 =" + nextpoint4 + ",nExtPoint5 = " + nextpoint5 + ",nExtPoint6 =" + nextpoint6 + ",nExtPoint7 =" + nextpoint7 + " " + setpass + " WHERE cAccName='" + username + "'";
            db.excuteUpdate(sql2);
            return true;
        }
        public bool checkUser(string username)
        { 
            string sql="SELECT cAccName FROM Account_sub_Info WHERE cAccName = '"+username+"'";
            DataTable t = db.excuteTable(sql);
            return (t.Rows.Count > 0 ? true : false);
        }
        public bool checkEmail(string email)
        {
            string sql = "SELECT cAccName FROM Account_sub_Info WHERE cEMail  = '" + email + "'";
            DataTable t = db.excuteTable(sql);
            return (t.Rows.Count > 0 ? true : false);
        }
        public int setKNB(string username, int howmany)
        {
            string sql = "update Account_Info set nExtPoint1='" + howmany + "' where cAccName='" + username + "'";
            return db.excuteUpdate(sql);
        
        }
        public DataTable getAccountInfo(string username)
        {
            string sql = "select * from Account_Info where cAccName='" + username + "'";
            DataTable t = db.excuteTable(sql);
            return t;
        }
        public DataTable getAccountSubInfo(string username)
        {
            string sql = "select * from Account_sub_Info where cAccName='" + username + "'";
            DataTable t = db.excuteTable(sql);
            return t;
        }
        public int setCongKNB(string username, int howmany)
        {
            string sql = "update Account_Info set nExtPoint1=nExtPoint1+'" + howmany + "' where cAccName='" + username + "'";
            return db.excuteUpdate(sql);
        }
        public DataTable ListAccount()
        {
            string sql = "SELECT top 100 * FROM Account_Info order by nExtPoint1 DESC";
            DataTable t = db.excuteTable(sql);
            return t;
        }
    }
}